﻿CREATE PROCEDURE [dbo].[proc_Finance_Report_Create]
	(
		@CompanyId int,
		@ShengChengDate nvarchar(10)
	)
AS
Begin
	Declare @RecordCount datetime
	Select @RecordCount=Count(Id) From Finance_Report Where CompanyId=@CompanyId and Convert(nchar(10),ShengChengDate,120)=@ShengChengDate
	if(@RecordCount=0)
		begin
			Insert	Into Finance_Report(CompanyId,CompanyName,CashIn,CashOut,BankIn,BankOut,XZML,XFML,XZYS,CostPriceAll,CostPriceFinish,WKSR,CreateDate,ShengChengDate)
			Select
			c.Id,
			c.CompanyName,
			ISNULL((Select Sum(Income) From Finance_RunningAccount Where CompanyId=c.Id And PaymentTypeId=1 And Convert(nchar(10),TradeDate,120)=@ShengChengDate),0) As CashIn,--现金收入总计
			ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id And PaymentTypeId=1 And Convert(nchar(10),TradeDate,120)=@ShengChengDate),0) As CashOut,--现金支出总计
			ISNULL((Select Sum(Income) From Finance_RunningAccount Where CompanyId=c.Id And PaymentTypeId<>1 And Convert(nchar(10),TradeDate,120)=@ShengChengDate),0) As BankIn,--银行收入总计
			ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id And PaymentTypeId<>1 And Convert(nchar(10),TradeDate,120)=@ShengChengDate),0) As BankOut,--银行支出总计
			ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=c.Id And TypeId=1 And Convert(nchar(10),CreateDate,120)=@ShengChengDate),0) As XZML,--新增毛利
			ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=c.Id And TypeId=2 And Convert(nchar(10),CreateDate,120)=@ShengChengDate),0) As XFML,--续费毛利
			ISNULL((Select Sum(YingShouPrice-ShiShouPrice) From [Order] Where CompanyId=c.Id And Convert(nchar(10),TradeDate,120)=@ShengChengDate),0) As XZYS,--新增应收
			ISNULL((Select Sum(CostPrice) From Project Where CompanyId=c.Id  And  Convert(nchar(10),CreateDate,120)=@ShengChengDate),0) As CostPriceAll,--新增项目成本
			ISNULL((Select Sum(CostPrice) From Project Where CompanyId=c.Id  And  Convert(nchar(10),CreateDate,120)=@ShengChengDate And IsFinish=1),0) As CostPriceFinish,--已完成项目成本
			ISNULL((Select Sum(Income) From Finance_RunningAccount Where CompanyId=c.Id And IOTypeId=2 And Convert(nchar(10),TradeDate,120)=@ShengChengDate),0) As WKSR,--尾款收入
			getdate(),
			@ShengChengDate
			From Company c Where c.Id=@CompanyId
		end
End
	RETURN